package com.pms.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.pms.entity.Task;
import com.pms.util.DBHelp;
import com.pms.util.RowMapper;

public class TaskDao {
	
	DBHelp<Task> db = new DBHelp<Task>();

	public void save(Task task) {
		String sql = "INSERT INTO t_task(tasktitle, stime, endtime, `desc`, state, `level`, rate, t_goal_id, t_project_proid, t_user_useid) VALUE(?,?,?,?,?,?,?,?,?,?)";
		db.executeSQL(sql, task.getTask_name(), task.getTask_starttime(), task.getTask_endtime(), task.getTask_context(), task.getState(), task.getLevel(), task.getRate(), task.getGoal_id(), task.getPro_id(), task.getUser_id());
	}

	public List<Task> findAllTask() {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT t_user.`username`, t_task.taskid, t_task.tasktitle, t_task.stime, t_task.endtime, t_task.`desc`, t_task.state, t_task.`level`, t_task.rate, t_task.createtime, t_task.t_goal_id, t_task.t_project_proid, t_task.t_user_useid  ");
		sb.append(" FROM t_task, t_user ");
		sb.append(" WHERE t_task.`t_user_useid` = t_user.`useid`");
		return db.executeQueryToList(sb.toString(), new TaskRowMapper());
	}
	
	public List<Task> findAllTaskByGoalId( String goalId) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT t_user.`username`, t_task.taskid, t_task.tasktitle, t_task.stime, t_task.endtime, t_task.`desc`, t_task.state, t_task.`level`, t_task.rate, t_task.createtime, t_task.t_goal_id, t_task.t_project_proid, t_task.t_user_useid  ");
		sb.append(" FROM t_task, t_user  ");
		sb.append(" WHERE t_task.`t_user_useid` = t_user.`useid` AND t_task.t_goal_id = ?");
		return db.executeQueryToList(sb.toString(), new TaskRowMapper(), goalId);
	}
	
	public double rate(String goalId){
		String sql = "SELECT AVG(rate) FROM t_task WHERE t_goal_id = ?";
		return db.executeQueryToNumber(sql, goalId).doubleValue();
	}
	
	public void updateTaskById(Task task, String taskId) {
		String sql = " UPDATE t_task SET tasktitle = ?, stime = ?, endtime = ?, `desc` = ?, state = ?, `level` = ?, rate = ?, t_goal_id = ?, t_project_proid = ?, t_user_useid = ? WHERE taskid = ?  ";
		db.executeSQL(sql, task.getTask_name(), task.getTask_starttime(), task.getTask_endtime(), task.getTask_context(), task.getState(), task.getLevel(), task.getRate(), task.getGoal_id(), task.getPro_id(), task.getUser_id(), taskId);
	}
	
	public Task findTaskById(String taskId) {
		String sql = "SELECT tasktitle, stime, endtime, `desc`, state, `level`, rate, t_goal_id, t_project_proid, t_user_useid FROM t_task where taskid = ?";
		return db.executeQueryToObject(sql, new RowMapper<Task>(){
			public Task mapRow(ResultSet rs) throws SQLException {
				Task task = new Task();
				task.setTask_name(rs.getString("tasktitle"));
				task.setTask_starttime(rs.getString("stime"));
				task.setTask_endtime(rs.getString("endtime"));
				task.setTask_context(rs.getString("desc"));
				task.setState(rs.getString("state"));
				task.setLevel(rs.getString("level"));
				task.setRate(rs.getInt("rate"));
				task.setGoal_id(rs.getString("t_goal_id"));
				task.setPro_id(rs.getString("t_project_proid"));
				task.setUser_id(rs.getString("t_user_useid"));
				return task;
			}
			
		}, taskId);
	}
	
	private class TaskRowMapper implements RowMapper<Task>{

		@Override
		public Task mapRow(ResultSet rs) throws SQLException {
			Task task = new Task();
			task.setTask_id(rs.getString("taskid"));
			task.setTask_name(rs.getString("tasktitle"));
			task.setTask_starttime(rs.getString("stime"));
			task.setTask_endtime(rs.getString("endtime"));
			task.setTask_context(rs.getString("desc"));
			task.setState(rs.getString("state"));
			task.setLevel(rs.getString("level"));
			task.setRate(rs.getInt("rate"));
			task.setCreatetime(rs.getString("createtime"));
			task.setGoal_id(rs.getString("t_goal_id"));
			task.setPro_id(rs.getString("t_project_proid"));
			task.setUser_id(rs.getString("t_user_useid"));
			task.setUsername(rs.getString("username"));
			return task;
		}
		
	}

	

	
}
